package de.fhb.itunes.repository.DataAccess;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import de.fhb.itunes.repository.WrongVoMap;
import de.fhb.itunes.repository.data.Musik_VO;
import de.fhb.itunes.repository.jdbc.JdbcDAOBase;
import de.fhb.itunes.repository.jdbc.DataAccessException;


public class DAO_Musik extends JdbcDAOBase implements IDAO_Musik {

	public DAO_Musik(){
		super();
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<Musik_VO> getAll() {
		List vos;
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		String sqlStr = "SELECT * FROM  [MediaFileSatz], [MediaFileSatz_Musiktitel] WHERE [MediaFileSatz].[ID] = [MediaFileSatz_Musiktitel].[ID]";
		try {
			con = getConnection();
			ps = con.prepareStatement(sqlStr);
			rs = ps.executeQuery();

			vos = transformSet(rs);
		} catch (SQLException sqle) {
			throw new DataAccessException("Error executing " + sqlStr, sqle);
		} finally {
			try {
				// close the resources
				if (ps != null)
					ps.close();
				if (con != null)
					con.close();
			} catch (SQLException sqlee) {
				throw new DataAccessException("Can't close connection", sqlee);
			}
		}
		List<Musik_VO> ret= null;
		try{
			ret = (List<Musik_VO>)vos;
		} catch (Exception e){
			throw new WrongVoMap("Die Daten aus der DB konnten nicht in ein Musik-Vo umgewandelt werden");
		}
		
		return ret;
	}

	@Override
	public Musik_VO getbyID(int id) {
		Musik_VO vo;
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int key = -1;

		String sqlStr = "SELECT * FROM  [MediaFileSatz] media,   [MediaFileSatz_Musiktitel] musik WHERE media.id = musik.id AND musik.id = ?";

		try {
			System.out.println("before Connection");
			con = getConnection();
			System.out.println("after Connection");
			ps = con.prepareStatement(sqlStr);
			ps.setInt(1, id);
			System.out.println("Statement");
			rs = ps.executeQuery();
			System.out.println("Query");
			vo = (Musik_VO) transformSetToVO(rs);
		} catch (SQLException sqle) {
			throw new DataAccessException("Error executing " + sqlStr, sqle);
		} finally {
			try {
				// close the resources
				if (ps != null)
					ps.close();
				if (con != null)
					con.close();
			} catch (SQLException sqlee) {
				throw new DataAccessException("Can't close connection", sqlee);
			}
		}
		return vo;
	}

	@Override
	public List<Musik_VO> getbyTitelname(String titel) {
		List vos;
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		String sqlStr = "SELECT * FROM  [MediaFileSatz] media,   [MediaFileSatz_Musiktitel] musik WHERE media.id = musik.id AND media.Titel like ?";
		try {
			con = getConnection();
			ps = con.prepareStatement(sqlStr);
			ps.setString(1, "%" + titel + "%");
			rs = ps.executeQuery();

			vos = transformSet(rs);
		} catch (SQLException sqle) {
			throw new DataAccessException("Error executing " + sqlStr, sqle);
		} finally {
			try {
				// close the resources
				if (ps != null)
					ps.close();
				if (con != null)
					con.close();
			} catch (SQLException sqlee) {
				throw new DataAccessException("Can't close connection", sqlee);
			}
		}
		return vos;
	}

	@Override
	public List<Musik_VO> getbyAlbum(String album) {
		List vos;
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		String sqlStr = "SELECT * FROM  [MediaFileSatz], [MediaFileSatz_Musiktitel] WHERE [MediaFileSatz].ID = [MediaFileSatz_Musiktitel].ID AND [MediaFileSatz_Musiktitel].Album = ?";
		try {
			con = getConnection();
			ps = con.prepareStatement(sqlStr);
			ps.setString(1,  album );
			rs = ps.executeQuery();

			vos = transformSet(rs);
		} catch (SQLException sqle) {
			throw new DataAccessException("Error executing " + sqlStr, sqle);
		} finally {
			try {
				// close the resources
				if (ps != null)
					ps.close();
				if (con != null)
					con.close();
			} catch (SQLException sqlee) {
				throw new DataAccessException("Can't close connection", sqlee);
			}
		}
		return vos;
	}
	public List<Musik_VO> getbyAlbum(String album, Connection con) {
		List vos;
		PreparedStatement ps = null;
		ResultSet rs = null;
		boolean conNeu = false;
		String sqlStr = "SELECT * FROM  [MediaFileSatz], [MediaFileSatz_Musiktitel] WHERE [MediaFileSatz].ID = [MediaFileSatz_Musiktitel].ID AND [MediaFileSatz_Musiktitel].Album = ?";
		try {
			if (con ==null ){
				con = getConnection();
				conNeu = true;
			}
			ps = con.prepareStatement(sqlStr);
			ps.setString(1,  album );
			rs = ps.executeQuery();

			vos = transformSet(rs);
		} catch (SQLException sqle) {
			throw new DataAccessException("Error executing " + sqlStr, sqle);
		} finally {
			try {
				// close the resources
				if (ps != null)
					ps.close();
				if (conNeu &&con != null)
					con.close();
			} catch (SQLException sqlee) {
				throw new DataAccessException("Can't close connection", sqlee);
			}
		}
		return vos;
	}

	@Override
	public HashMap<Integer, Musik_VO> getbyAlbumAsHash(String album) {
		List<Musik_VO> musiklist = getbyAlbum(album);
		HashMap<Integer, Musik_VO> ret = new HashMap<Integer, Musik_VO>();
		for (Musik_VO musik_VO : musiklist) {
			ret.put(musik_VO.getTitelnummer(), musik_VO);
		}
		return ret;
	}
	public HashMap<Integer, Musik_VO> getbyAlbumAsHash(String album, Connection con) {
		List<Musik_VO> musiklist = getbyAlbum(album,con);
		HashMap<Integer, Musik_VO> ret = new HashMap<Integer, Musik_VO>();
		for (Musik_VO musik_VO : musiklist) {
			ret.put(musik_VO.getTitelnummer(), musik_VO);
		}
		return ret;
	}

	@Override
	protected Object map2VO(ResultSet rs) throws SQLException {
		Musik_VO musik = new Musik_VO();
		musik.setAdded(rs.getDate("Added"));
		musik.setAlbum(rs.getString("Album"));
		musik.setGenre(rs.getString("Genre"));
		musik.setId(rs.getInt("ID"));
		musik.setLaenge(rs.getLong("Länge"));
		musik.setPfad(rs.getString("Pfad"));
		musik.setTitel(rs.getString("Titel"));
		musik.setTitelnummer(rs.getInt("Titelnummer"));
		musik.setTrackid(rs.getInt("TrackID"));
		musik.setAlbuminterpret(rs.getString("AlbumInterpret"));
		//TODO albuminterpret zur DB hinzufuegen
		//musik.setAlbuminterpret(rs.getString("Albuminterpret"));
		return musik;
	}

	@Override
	public List<Musik_VO> getbyAlbumAndAlbumInterpret(String album,
			String albuminterpret) {
		List vos;
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		String sqlStr = "SELECT * FROM  [itunes].[dbo].[MediaFileSatz] media,  [itunes].[dbo].[MediaFileSatz_Musiktitel] musik WHERE media.id = musik.id AND musik.Album like ? AND musik.Albuminterpret like ?";
		try {
			con = getConnection();
			ps = con.prepareStatement(sqlStr);
			ps.setString(1, "%" + album + "%");
			ps.setString(2, "%" + albuminterpret + "%");
			rs = ps.executeQuery();

			vos = transformSet(rs);
		} catch (SQLException sqle) {
			throw new DataAccessException("Error executing " + sqlStr, sqle);
		} finally {
			try {
				// close the resources
				if (ps != null)
					ps.close();
				if (con != null)
					con.close();
			} catch (SQLException sqlee) {
				throw new DataAccessException("Can't close connection", sqlee);
			}
		}
		return vos;
	}

	@Override
	public List<Musik_VO> getbyArtist(String artist) {
		List vos;
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		String sqlStr = "SELECT * FROM  [MediaFileSatz] media,   [MediaFileSatz_Musiktitel] musik WHERE media.id = musik.id AND musik.Interpret like ?";
		try {
			con = getConnection();
			ps = con.prepareStatement(sqlStr);
			ps.setString(1, "%" + artist + "%");
			rs = ps.executeQuery();

			vos = transformSet(rs);
		} catch (SQLException sqle) {
			throw new DataAccessException("Error executing " + sqlStr, sqle);
		} finally {
			try {
				// close the resources
				if (ps != null)
					ps.close();
				if (con != null)
					con.close();
			} catch (SQLException sqlee) {
				throw new DataAccessException("Can't close connection", sqlee);
			}
		}
		return vos;
	}
	public List<Musik_VO> searchbyKeyword(String keyword){
		List vos;
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		String sqlStr = "SELECT * FROM  [MediaFileSatz] media,   [MediaFileSatz_Musiktitel] musik WHERE media.id = musik.id AND (musik.Interpret like ? OR media.Titel = ? )";
		try {
			con = getConnection();
			ps = con.prepareStatement(sqlStr);
			ps.setString(1, "%" + keyword + "%");
			ps.setString(2, "%" + keyword + "%");
			rs = ps.executeQuery();

			vos = transformSet(rs);
		} catch (SQLException sqle) {
			throw new DataAccessException("Error executing " + sqlStr, sqle);
		} finally {
			try {
				// close the resources
				if (ps != null)
					ps.close();
				if (con != null)
					con.close();
			} catch (SQLException sqlee) {
				throw new DataAccessException("Can't close connection", sqlee);
			}
		}
		return vos;
	}

}
